BigQuery で JSON Lines ではないカンマ区切りの JSON 形式のファイルを読み込む裏技 #cm_google_cloud_adcal_2024

クラスメソッドのGoogle Cloud Advent Calendar 2024

#Google Cloud (GCP)

#Google BigQuery

#Google Cloud Storage

#JSON

hanzawa.yuya

2024.12.13

Google Cloud データエンジニアのはんざわです。
クラスメソッドの Google Cloud Advent Calendar 2024 の 13 日目のブログです。

BigQuery を利用したデータ分析基盤を構築する際、Cloud Storage に保存された JSON ファイルを読み込みたいケースは多いと思います。
しかし、BigQuery では、JSON ファイルが JSON Lines 形式でない場合、公式ドキュメントに記載された通常の手順では読み込むことができません。

そこで本ブログでは、以下の Google Cloud の公式ブログで紹介されている方法を参考に、カンマ区切りの JSON 形式のファイルを BigQuery に読み込む方法を紹介します。

BigQuery で JSON ファイルを読み込む際の課題

改行区切りの JSON 形式（JSON Lines）の場合

BigQuery では、Cloud Storage から JSON ファイルを読み込む場合、JSON データは JSON Lines（改行区切り）である必要があります。
JSON Lines とは、1 行に 1 つの JSON オブジェクトを記述する形式です。例えば、以下のような形式です。

sample_lines.json

{"id": 1, "name": "apple"}
{"id": 2, "name": "banana"}

この形式であれば、以下のような外部テーブルを作成し、参照することができます。

# 外部テーブルを作成
CREATE EXTERNAL TABLE `json.sample_table_1`
OPTIONS(
  format="NEWLINE_DELIMITED_JSON",
  uris=["gs://json-files-test/jsonl/sample_lines.json"]
);

# 参照してみる
SELECT
  *
FROM
  json.sample_table_1

実行結果

+----+--------+
| id |  name  |
+----+--------+
|  1 | apple  |
|  2 | banana |
+----+--------+

カンマ区切りの JSON 形式の場合

一方、JSON ファイルが JSON Lines 形式ではなく、カンマ区切りの JSON 形式で記述されている場合、そのままでは読み込むことができません。
例えば、以下のようなファイルを外部テーブルとして読み込もうとするとエラーが発生し、読み込むことができません。

sample_1.json

{
  "id": 1,
  "name": "apple"
}

sample_2.json

{
  "id": 2,
  "name": "banana"
}

CREATE EXTERNAL TABLE `json.sample_table_2`
OPTIONS(
  format="NEWLINE_DELIMITED_JSON",
  uris=["gs://json-files-test/json/sample_*.json"]
);

実行結果

Error while reading table: sample_table_2, error message: Failed to parse JSON: Unexpected end of string; Unexpected end of string; Expected key File: sample_2.json

このエラーは、BigQuery が JSON ファイルを 1 行ごとに独立した JSON オブジェクトとして解析するために発生します。

問題点

データ基盤の構築において、データソースとして受け取る JSON ファイルが JSON Lines 形式で提供されることは少なく、カンマ区切りの JSON 形式で提供されるケースがほとんどだと思います。
そのため、BigQuery で読み込むためには、事前に JSON Lines 形式への変換が必要です。もちろん、この変換処理を実装し、テスト・運用するには手間がかかります。

カンマ区切りの JSON を BigQuery で読み込む裏技

カンマ区切りの JSON ファイルを BigQuery でそのまま読み込む裏技として、JSON ファイル全体を 1 列の CSV として読み込み、BigQuery の JSON 関数で適切な JSON 型に解析する方法があります。

この方法では、以下の 2 つのステップを実行します。

CSV として JSON ファイルを読み込む外部テーブルを作成する
外部テーブルから読み込んだデータを JSON 型にパースする

1. CSV として JSON ファイルを読み込む外部テーブルを作成する

カンマ区切りの JSON ファイルをそのまま BigQuery で読み込むことはできないため、まずは JSON ファイル全体を 1 列の文字列として扱う外部テーブルを作成します。

通常、JSON ファイルを BigQuery に取り込む場合、フォーマット（format）に NEWLINE_DELIMITED_JSON（もしくはJSON）を指定します。しかし、ここでは CSV を指定することで、JSON データ全体を 1 列の文字列形式として読み込みます。

この際、以下の設定を行います。

区切り文字（field_delimiter）：\x10
- Control-P 区切り文字（ASCIIコード 0x10）は非表示の制御文字で、通常のデータに含まれる可能性は極めて低く、JSON オブジェクトを 1 行ごとに区切るための文字として適切です。
- また、他の特殊文字でもデータに影響がない場合は利用可能です。
引用符（quote）：''
- 引用符に空文字列を指定しています。
- JSON を CSV として解析したときにデフォルトの引用文字（ダブルクォーテーション / "）の影響が出ないようにしています。

CREATE EXTERNAL TABLE `json.sample_table_3` (
  col1 STRING
)
OPTIONS (
  format = 'CSV',
  field_delimiter = '\x10',
  quote = '',
  uris = ['gs://json-files-test/json/sample_*.json']
);

試しに、この外部テーブルを参照すると次のようになります。

SELECT
  *
FROM
  `json.sample_table_3`

スクリーンショット 2024-12-09 17.03.47

2. 外部テーブルから読み込んだデータを JSON 型にパース

次に、先ほどの文字列を結合し、JSON 型にパースします。
ファイル毎に 1 つの JSON オブジェクトとしてパースする必要があるため、ファイル名の擬似列でグルーピングします。

SELECT
  PARSE_JSON(STRING_AGG(col1, '')) AS JsonObject
FROM
  `json.sample_table_3`
GROUP BY _FILE_NAME

上記のクエリを実行した結果、以下のような JSON オブジェクトを取得することができました。

スクリーンショット 2024-12-09 19.03.40

このまま出力結果を JSON 型としてテーブルに保存しても良いですし、JSON オブジェクトにアクセスした結果を保存することもできます。

SELECT
  JsonObject.id,
  JsonObject.name
FROM
  (
    SELECT
      PARSE_JSON(STRING_AGG(col1, '')) AS JsonObject
    FROM
      `json.sample_table_3`
    GROUP BY _FILE_NAME
  )

上記のクエリを実行した結果、以下のような JSON オブジェクトにアクセスしたデータを取得することができました。

スクリーンショット 2024-12-09 21.26.57

注意点

冒頭で説明した通り、この方法は公式ドキュメントには記載されておらず、公式ブログで紹介されていた内容を参考にしています。本番環境で利用する場合は、この点を考慮した上で利用してください。
BigQuery の行サイズは 100 MB までに制限されています。この読み込み方法では、JSON ファイルの全てのデータを 1 列の行に読み込むため、JSON ファイルのサイズが 100 MB を超えないようにしてください。

おまけ

最後におまけとして、いくつかのケースでこの裏技を利用することができるのか検証してみたいと思います。

１. JSON 配列

最初に、JSON 配列で試してみます。
JSON 配列とは、配列の要素が JSON オブジェクトになっているデータ構造です。

具体的には、以下のような形式です。このファイルも同様の方法で読み込めるか試してみます。

array.json

[
    {
        "id": 1,
        "name": "apple"
    },
    {
        "id": 2,
        "name": "banana"
    }
]

まずは、外部テーブルを作成します。

CREATE OR REPLACE EXTERNAL TABLE `json.test1` (
  col1 STRING
)
OPTIONS (
  format = 'CSV',
  field_delimiter = '\x10',
  quote = '',
  uris = ['gs://json-files-test/test1/array.json']
);

以下のクエリで、JSON 配列を展開し、データを取得することができました。

PARSE_JSON で JSON 型にパースする箇所までは同じですが、そのままではアクセスできないため、JSON_QUERY_ARRAY で ARRAY<JSON> に変換後、UNNEST で展開しています。

SELECT
  _JsonObjectArray.id,
  _JsonObjectArray.name
FROM
  (
    SELECT
      JSON_QUERY_ARRAY(PARSE_JSON(STRING_AGG(col1, ''))) AS JsonObjectArray
    FROM
      `json.test1`
  )
LEFT JOIN
  UNNEST(JsonObjectArray) AS _JsonObjectArray

スクリーンショット 2024-12-12 22.09.40

２. 圧縮された JSON ファイル

次に、圧縮された JSON ファイルで試してみます。

外部テーブルを作成しますが、圧縮されたファイルを読み込みたいので compression に GZIP を指定しています。

CREATE EXTERNAL TABLE `json.test2` (
  col1 STRING
)
OPTIONS (
  format = 'CSV',
  compression = 'GZIP',
  field_delimiter = '\x10',
  quote = '',
  uris = ['gs://json-files-test/test2/zip*.json.gz']
);

このような外部テーブルを作成した場合、期待通りにデータを取得することができました。

SELECT
  JsonObject.id,
  JsonObject.name
FROM
  (
    SELECT
      PARSE_JSON(STRING_AGG(col1, '')) AS JsonObject
    FROM
      `json.test2`
    GROUP BY _FILE_NAME
  )

スクリーンショット 2024-12-12 22.56.33

注意点として、BigQuery では gzip 圧縮されたファイルを並列で読み込むことができず、直列で読み込まれてしまいます。データ量によっては、処理速度が非常に遅くなるため、注意が必要です。

gzip 圧縮を使用した場合、BigQuery はデータを並列で読み取ることができません。圧縮された CSV データを BigQuery に読み込む場合は、圧縮されていないデータを読み込むよりも時間がかかります。

参考：制限事項

まとめ

今回のブログでは、カンマ区切りの JSON 形式を BigQuery に読み込む方法を紹介しました。

この方法の最大のメリットは、JSON Lines形式への変換作業が不要になることです。例えば、データソースから受け取る JSON ファイルがカンマ区切り形式の場合、通常は変換処理を実装し、それをテストして運用する必要があります。しかし、この方法を使えば、そうした手間を省けるため、データ分析までの時間を大幅に短縮することができます。

ただし、この方法は公式ドキュメントに記載されているものではなく、BigQuery の行サイズ制限などの注意点も存在します。本番環境で利用する際には、これらの点を十分に検討した上で導入するようにしてください。

ぜひ、参考にしてみてください！

（筆者としては、ネイティブ機能で読み込めるようになる日が来ることを願っています...）

明日 12/14 は suto さんです。よろしくお願いします！